/**
 * 
 */
package com.kahweh.android.ngphoto.db;

import java.io.IOException;

import org.xmlpull.v1.XmlPullParserException;

import android.content.Context;
import android.content.res.Resources;
import android.content.res.XmlResourceParser;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteCursorDriver;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQuery;
import android.util.Log;

import com.kahweh.android.ngphoto.R;

/**
 * @author Michael
 *
 */
public class NGPhotoDatabase extends SQLiteOpenHelper {

	private static final String CLS_NAME = NGPhotoDatabase.class.getName();
	private static final String DATABASE_NAME = "NGPhoto";
	private static final int DATABASE_VERSION = 1;
	private final Context mCtx;
	
	public NGPhotoDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.mCtx = context;
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite.SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		//create table
		try {
			db.beginTransaction();
			db.execSQL(mCtx.getString(R.string.ngphoto_create_table));
			db.setTransactionSuccessful();
		} catch (SQLException e) {
			Log.e(CLS_NAME, "SQL Error!", e);
			return;
		} finally {
			db.endTransaction();
		}

		String date = null;
		String title = null;
		String author = null;
		String des = null;
		//Insert templete
		String sqlTmp = mCtx.getString(R.string.ngphoto_insert_templete);
		Resources res = mCtx.getResources();
		try {
			db.beginTransaction();
			XmlResourceParser xrp = res.getXml(R.xml.pod);
			while (xrp.getEventType() != XmlResourceParser.END_DOCUMENT) {
				if (xrp.getEventType() == XmlResourceParser.START_TAG) {
					String str = xrp.getName();
					if (str.equalsIgnoreCase("photo")) {
						date = xrp.getAttributeValue(null, "date");
						title = xrp.getAttributeValue(null, "title");
						author = xrp.getAttributeValue(null, "author");
					}
				} else if (xrp.getEventType() == XmlResourceParser.TEXT) {
					des = xrp.getText();
					db.execSQL(sqlTmp, new Object[]{date, title, author, des});
				}
				xrp.next();
			}
			db.setTransactionSuccessful();
			xrp.close();
		} catch (XmlPullParserException e) {
			Log.e(CLS_NAME, "Parse pod.xml file error.", e);
		} catch (IOException e) {
			Log.e(CLS_NAME, "Cannot read resource file.", e);
		} catch (SQLException e) {
			Log.e(CLS_NAME, "Insert photo error.", e);
		} finally {
			db.endTransaction();
		}
	}

	/* (non-Javadoc)
	 * @see android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite.SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	public PhotoCursor getPhotoByDate(String date) {
		String sql = PhotoCursor.QUERY + "date = '" + date + "'";
		return getPhotoCursor(sql);
	}

	public PhotoCursor getPhotosByCategory(String cat) {
		String sql = PhotoCursor.QUERY + "category = '" + cat + "'";
		return getPhotoCursor(sql);
	}

	public PhotoCursor getPhotoCursor(String sql) {
		SQLiteDatabase db = getReadableDatabase();
		PhotoCursor cursor = (PhotoCursor)db.rawQueryWithFactory(new PhotoCursor.Factory(), 
				sql, null, null);
		cursor.moveToFirst();
		return cursor;
	}

	public static class PhotoCursor extends SQLiteCursor{

		private static final String QUERY = "SELECT date, title, author, des FROM ngphoto WHERE ";
		
		private PhotoCursor(SQLiteDatabase db, SQLiteCursorDriver driver,
				String editTable, SQLiteQuery query) {
			super(db, driver, editTable, query);
		}
		
		private static class Factory implements SQLiteDatabase.CursorFactory {

			@Override
			public Cursor newCursor(SQLiteDatabase db,
					SQLiteCursorDriver masterQuery, String editTable,
					SQLiteQuery query) {
				return new PhotoCursor(db, masterQuery, editTable, query);
			}
		}
		
		public String getDate() {
			return getString(getColumnIndexOrThrow("date"));
		}

		public String getTitle() {
			return getString(getColumnIndexOrThrow("title"));
		}

		public String getAuthor() {
			return getString(getColumnIndexOrThrow("author"));
		}

		public String getDes() {
			return getString(getColumnIndexOrThrow("des"));
		}
	}
}
